package com.xjt.query.action;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import net.sf.cglib.beans.BeanCopier;

import org.apache.commons.collections.CollectionUtils;
import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;

import com.xjt.common.DateUtils;
import com.xjt.common.PageInfo;
import com.xjt.common.StringUtils;
import com.xjt.common.Types.DB_TYPE;
import com.xjt.common.Types.INPUT_TYPE;
import com.xjt.query.dao.interf.ConditionColumnInfoDao;
import com.xjt.query.dao.interf.DisplayColumnInfoDao;
import com.xjt.query.dao.interf.InformationSchemaColumnsDao;
import com.xjt.query.dao.pojo.ConditionColumnInfo;
import com.xjt.query.dao.pojo.DisplayColumnInfo;
import com.xjt.query.dao.pojo.InformationSchemaColumns;
import com.xjt.query.entity.ConditionColumnInfoVo;
import com.xjt.query.service.interf.QueryListService;

public abstract class QueryListAbstractAction extends BaseAction{

    private static final long serialVersionUID = 8667713953660291107L;

    private static final Logger           log                 = Logger.getLogger(QueryListAbstractAction.class);

    private static final String           MYSQL_BY_PAGE       = " $sql$ limit :LIMIT offset :OFFSET";

    /** 使用condition1 and condition2来替换(不需要where) */
    protected static final String         WHERE_REPLACE_STR   = "$where$";

    /** 使用order by column来替换 */
    protected static final String         ORDERBY_REPLACE_STR = "$orderby$";

    private QueryListService              queryListService;

    private DisplayColumnInfoDao          displayColumnInfoDao;

    private ConditionColumnInfoDao        conditionColumnInfoDao;

    private InformationSchemaColumnsDao   informationSchemaColumnsDao;

    protected PageInfo                    pageInfo;

    protected List<DisplayColumnInfo>     displayList;

    protected List<ConditionColumnInfoVo> conditionList;

    private static final BeanCopier       conditionToVoCopier = BeanCopier.create(ConditionColumnInfo.class,
                                                                                  ConditionColumnInfoVo.class, false);

    /**
     * 查询结果
     * 
     * @return
     */
    public List<Map<String, Object>> queryData() {
        // 获取查询sql
        String sql = getQuerySql();
        if (StringUtils.isBlank(sql)) {
            return null;
        }
        // 获取检索条件
        conditionList = querySearchColumnList();
        Map<String, Object> params = new HashMap<String, Object>();
        StringBuilder sWhere = new StringBuilder();
        sWhere.append(" 1 = 1 ");
        if (CollectionUtils.isNotEmpty(conditionList) && null != pageInfo) {
            for (ConditionColumnInfoVo condition : conditionList) {
                String value = getRequest().getParameter(condition.getColumnName());
                condition.setValue(value);
                fillConditonWhereParams(condition, sWhere, params);
            }
        }
        displayList = queryDisplayColumnList();
        // 替换where条件 (sWhere为 [ 1 = 1 and condition1 and ...],所以sql应该为 select * from table where condition and $where$)
        sql = sql.replace(WHERE_REPLACE_STR, sWhere.toString());

        if (null == pageInfo) {
            pageInfo = new PageInfo();
        }
        String countSql = getCountSql(sql);
        int totalCount = queryListService.countQueryResult(countSql, params);
        // 统计分页信息
        pageInfo.doPage(pageInfo.getPageNo(), totalCount);

        int pageNo = pageInfo.getPageNo();
        int pageSize = pageInfo.getPageSize();

        switch (getDBType()) {
            case DB_TYPE.MYSQL:
                sql = MYSQL_BY_PAGE.replace("$sql$", sql);
                int limit = pageSize;
                int offset = (pageNo - 1) * pageSize;
                params.put("LIMIT", limit);
                params.put("OFFSET", offset);
                break;
            // TODO 其他数据库 需要的时候加上
            case DB_TYPE.ORACLE:
            default:
                break;
        }
        return queryListService.queryList(sql, params);
    }

    /**
     * 根据检索条件，获取where信息和参数信息
     * 
     * @param condition 检索条件
     * @param where
     * @param params
     */
    private void fillConditonWhereParams(ConditionColumnInfoVo condition, StringBuilder where,
                                         Map<String, Object> params) {
        if (null == condition || null == where || null == params) {
            return;
        }
        // 双日期控件
        switch (condition.getInputType()) {
            case INPUT_TYPE.DOUBLE_DATE:
                List<String> valueList = condition.getValueList();
                if (CollectionUtils.isNotEmpty(valueList) && valueList.size() == 2) {
                    String from = valueList.get(0);
                    String to = valueList.get(1);
                    Date dateFrom = DateUtils.toDate(from, "yyyy-MM-dd");
                    Date dateTo = DateUtils.toDate(to, "yyyy-MM-dd");
                    String columnName = condition.getColumnName();
                    if (null != dateFrom) {
                        where.append(" and ").append(columnName).append(" >= :").append(columnName + "From");
                        params.put(columnName + "From", dateFrom);
                    }
                    if (null != dateTo) {
                        dateTo = DateUtils.getFutureDayInDays(dateTo, 1);
                        where.append(" and ").append(columnName).append(" < :").append(columnName + "To ");
                        params.put(columnName + "To", dateTo);
                    }
                }
                break;
            case INPUT_TYPE.TEXT:
                if (StringUtils.isNotBlank(condition.getValue())) {
                    // 如果为完全匹配
                    if (null != condition.getIsCompleteMatch() && condition.getIsCompleteMatch() == 1) {
                        where.append(" and ").append(condition.getColumnName()).append(" = :").append(condition.getColumnName());
                        params.put(condition.getColumnName(), condition.getValue());
                    }
                    // 如果为部分匹配
                    else {
                        where.append(" and ").append(condition.getColumnName()).append(" like :").append(condition.getColumnName());
                        // 重新设置paramMap中的值（前后添加%）
                        params.put(condition.getColumnName(), "%" + condition.getValue() + "%");
                    }
                }
                break;
            case INPUT_TYPE.DATE:
                Date date = DateUtils.toDate(condition.getValue(), "yyyy-MM-dd");
                if (null != date) {
                    where.append(" and ").append(condition.getColumnName()).append(" = :").append(condition.getColumnName());
                    params.put(condition.getColumnName(), date);
                }
                break;
            case INPUT_TYPE.SELECT:
                if (StringUtils.isNotBlank(condition.getValue())) {
                    where.append(" and ").append(condition.getColumnName()).append(" = :").append(condition.getColumnName());
                    params.put(condition.getColumnName(), condition.getValue());
                }
                break;
            default:
                if (StringUtils.isNotBlank(condition.getValue())) {
                    where.append(" and ").append(condition.getColumnName()).append(" = :").append(condition.getColumnName());
                    params.put(condition.getColumnName(), condition.getValue());
                }
                break;
        }
    }

    /**
     * 获取查询SQL
     * 
     * @return 查询SQL
     */
    protected String getQuerySql() {
        String key = this.getKey();
        if (StringUtils.isBlank(key)) {
            return null;
        }
        return "select * from " + key + " where " + WHERE_REPLACE_STR + " " + ORDERBY_REPLACE_STR;
    }

    protected abstract String getKey();

    /**
     * 使用的数据库类型（默认为mysql，如果为其他，则重写此方法）
     * 
     * @return 数据库类型
     */
    protected int getDBType() {
        return DB_TYPE.MYSQL;
    }

    /**
     * 获取查询结果数量的SQL
     * 
     * @param sql 查询SQL
     * @return 查询结果数量的SQL
     */
    protected String getCountSql(String sql) {
        if (StringUtils.isBlank(sql)) {
            return null;
        }
        String lowerSql = sql.toLowerCase();
        String newSql = sql;
        int staIdx = lowerSql.indexOf("select ");
        int endIdx = lowerSql.indexOf(" from");
        if (staIdx >= 0 && endIdx >= 0) {
            newSql = sql.substring(0, staIdx + 6) + " count(*) " + sql.substring(endIdx);
        }
        return newSql;
    }

    /**
     * 获取显示列的信息列表
     * 
     * @return
     */
    protected List<DisplayColumnInfo> queryDisplayColumnList() {
        List<DisplayColumnInfo> result = null;
        String key = this.getKey();
        if (StringUtils.isBlank(key)) {
            return result;
        }
        try {
            DisplayColumnInfo query = new DisplayColumnInfo();
            query.setKeyWord(key);
            result = displayColumnInfoDao.queryColumnInfoList(query);
            if (CollectionUtils.isNotEmpty(result)) {
                return result;
            }
        } catch (Exception ex) {
            log.warn("", ex);
        }
        // 根据数据库类型
        switch (getDBType()) {
            case DB_TYPE.MYSQL:
                InformationSchemaColumns query = new InformationSchemaColumns();
                query.setTableName(key);
                List<InformationSchemaColumns> columnList = informationSchemaColumnsDao.queryColumnList(query);
                if (CollectionUtils.isNotEmpty(columnList)) {
                    result = new ArrayList<DisplayColumnInfo>();
                    for (InformationSchemaColumns column : columnList) {
                        DisplayColumnInfo info = new DisplayColumnInfo();
                        info.setColumnName(column.getColumnName());
                        info.setColumnDisplayName(StringUtils.isBlank(column.getColumnComment()) ? column.getColumnName() : column.getColumnComment());
                        info.setDataType(column.getDataType());
                        info.setSort(column.getOrdinalPosition());
                        info.setKeyWord(key);
                        result.add(info);
                    }
                }
                break;
            // TODO 其他数据库 需要的时候加上
            case DB_TYPE.ORACLE:
            default:
                break;
        }
        return result;
    }

    /**
     * 获取检索条件列表
     * 
     * @return
     */
    protected List<ConditionColumnInfoVo> querySearchColumnList() {
        List<ConditionColumnInfoVo> result = null;
        String key = this.getKey();
        if (StringUtils.isBlank(key)) {
            return result;
        }
        try {
            ConditionColumnInfo query = new ConditionColumnInfo();
            query.setKeyWord(key);
            List<ConditionColumnInfo> columnList = conditionColumnInfoDao.queryColumnInfoList(query);
            if (CollectionUtils.isNotEmpty(columnList)) {
                result = new ArrayList<ConditionColumnInfoVo>();
                for (ConditionColumnInfo columnInfo : columnList) {
                    ConditionColumnInfoVo vo = new ConditionColumnInfoVo();
                    conditionToVoCopier.copy(columnInfo, vo, null);
                    result.add(vo);
                }
                return result;
            }
        } catch (Exception ex) {
            log.warn("", ex);
        }
        return null;
    }

    /**
     * 得到HttpServletRequest对象
     */
    protected HttpServletRequest getRequest() {
        return ServletActionContext.getRequest();
    }

    /**
     * @return the pageInfo
     */
    public PageInfo getPageInfo() {
        return pageInfo;
    }

    /**
     * @param pageInfo the pageInfo to set
     */
    public void setPageInfo(PageInfo pageInfo) {
        this.pageInfo = pageInfo;
    }

    /**
     * @return the displayList
     */
    public List<DisplayColumnInfo> getDisplayList() {
        return displayList;
    }

    /**
     * @param displayList the displayList to set
     */
    public void setDisplayList(List<DisplayColumnInfo> displayList) {
        this.displayList = displayList;
    }

    /**
     * @return the conditionList
     */
    public List<ConditionColumnInfoVo> getConditionList() {
        return conditionList;
    }

    /**
     * @param conditionList the conditionList to set
     */
    public void setConditionList(List<ConditionColumnInfoVo> conditionList) {
        this.conditionList = conditionList;
    }

    /**
     * @param queryListService the queryListService to set
     */
    public void setQueryListService(QueryListService queryListService) {
        this.queryListService = queryListService;
    }

    /**
     * @param displayColumnInfoDao the displayColumnInfoDao to set
     */
    public void setDisplayColumnInfoDao(DisplayColumnInfoDao displayColumnInfoDao) {
        this.displayColumnInfoDao = displayColumnInfoDao;
    }

    /**
     * @param conditionColumnInfoDao the conditionColumnInfoDao to set
     */
    public void setConditionColumnInfoDao(ConditionColumnInfoDao conditionColumnInfoDao) {
        this.conditionColumnInfoDao = conditionColumnInfoDao;
    }

    /**
     * @param informationSchemaColumnsDao the informationSchemaColumnsDao to set
     */
    public void setInformationSchemaColumnsDao(InformationSchemaColumnsDao informationSchemaColumnsDao) {
        this.informationSchemaColumnsDao = informationSchemaColumnsDao;
    }

}
